package user_client;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import library.user_creator.CreatorReturnedInformationPane;

/**
 * 
 * @author Pawel Rychly
 * 
 */
public class LibrarianUserClient extends DataBaseClient {

	public LibrarianUserClient(Connection connection, String userName,
			String userPassword) {

		super(connection, userName, userPassword);
		// TODO Auto-generated constructor stub
	}

	
	private boolean checkUser(String name, String surname, String address)
			throws SQLException {
		Statement stmt = this.getConnection().createStatement();
		
		ResultSet res = stmt.executeQuery("select count(*) as num from "
				+ "czytelnicy where ( replace(lower(imie),' ','')" +
					" = replace(lower('" + name + "'),' ', ''))" +
						" and ( replace(lower(nazwisko),' ','') " +
							" = replace(lower('" + surname + "'),' ', ''))" + 
							" and ( replace(lower(adres),' ','') " +
								" = replace(lower('" + address + "'),' ', ''))");
			
		res.next();
		Integer num = res.getInt("num");
		System.out.println("LibrarianUserClient: checking");
		if (num > 0) {
			return true;
		}

		return false;
	}

	/**
	 * Add new user to database;
	 * 
	 * @throws SQLException
	 */
	public boolean addClient(CreatorReturnedInformationPane pane,
			String name, String surname, String address)
			throws SQLException {
		// check does the client exists.
		// this.getConnection().setAutoCommit(false);
		try {
		if (!checkUser(name, surname, address)) {
			System.out.println("l-1");
			Statement stmt = this.getConnection().createStatement();
			// get the user id
			System.out.println("l-3");
			
			//ResultSet res = stmt.executeQuery(ALTER SEQUENCE )
			ResultSet res = stmt
					.executeQuery("select id_czytelnika_seq.NEXTVAL as ID from dual");
			res.next();
			Integer user_id = res.getInt("ID");
			System.out.println("l-4");
			// add user to table
			res = stmt.executeQuery("insert into czytelnicy	values("
					+ "id_czytelnika_seq.CURRVAL,'" + name + "', '" + surname
					+ "','" + address+ "')");

			// create user
			stmt.executeQuery("CREATE USER " + surname 	+ "_"
					+ user_id.toString() + " IDENTIFIED BY "
					+ "pass" + user_id.intValue() 
					+ " DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp "
					+ "QUOTA unlimited ON users  QUOTA unlimited ON " 
					+ "system PROFILE "
					+ "default ");

			stmt.executeQuery("grant create session, select any table," +
					" insert any table to "
					+ surname + "_" + user_id.toString());

			stmt.executeQuery("grant UserClient to " + surname + "_"
					+ user_id.toString());

			stmt.close();
			res.close();
			this.getConnection().commit();
			pane.getTextPane().getField("login").setText(
					surname + "_" + user_id.toString());
			pane.getTextPane().getField("temporary password").setText(
					"pass" + user_id.intValue());
			pane.setVisible(true);
			System.out.println("utworzono użytkownika");
			return true;
		}
		} catch (SQLException e) {
			System.out.println("SQLError");
			e.printStackTrace();
			this.getConnection().rollback();
		}
		
		
		
		// add user privileges
		/*
		 * stmt.executeQuery("grant " + user_id + "IDENTIFIED BY" + password +
		 * "DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp " +
		 * "QUOTA unlimited ON users + QUOTA unlimited ON system PROFILE " +
		 * "default ");
		 */		
		return false;
	}

	/**
	 * Removes user from system.
	 */
	public void removeClient() {

	}

	/**
	 * Accept borrowing book from library.
	 */
	public void approveBorrowingBook() {

	}

	/**
	 * Sends to database information about returning book.
	 */
	public void noteReturningOfBook() {

	}

	/**
	 * Adds book to database.
	 */
	public void addBook() {

	}

	/**
	 * Removes book from database.
	 */
	public void removeBook() {

	}

	/**
	 * Adds new section
	 */
	public void addNewSection() {

	}

	/**
	 * Removes section
	 */
	public void removeSection() {

	}
	
	/**
	 * Ads new department
	 */
	public Integer addDepartment(String name, String num) throws SQLException {
		ArrayList<String> cols = new ArrayList<String>();
		cols.add("nazwa");
		cols.add("nr_regalu");
		ArrayList<String> values = new ArrayList<String>();
		values.add(name);
		values.add(num);
		
		if (isExists(cols, values, "dzialy")){
			return -1;
		} else {
			Statement stmt = this.getConnection().createStatement();
			ResultSet res = stmt.executeQuery("select id_dzialu_seq.NEXTVAL as val from dual");
			res.next();
			Integer result = res.getInt("val");
			int val = stmt
					.executeUpdate("INSERT INTO dzialy VALUES (id_dzialu_seq.CURRVAL, '" + name + "', " + num +" )"); 
			this.getConnection().commit();
			stmt.close();
			System.out.println("INDEX:"+val);
			return result;
		}
		
		
	}
	
	

}
